package com.kyh.util.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.net.URL;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.alibaba.fastjson.JSON;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

public class ReadExcelUtil {
	public static void main(String[] args) {
//		// 此处为我创建Excel路径：E:/zhanhj/studysrc/jxl下
//		File file = new File("C:\\Users\\Administrator\\Desktop\\四方平台\\进件资料3\\总进件3.xls");
//		List<Map<Integer, String>> excelList = ReadExcelUtil.readSheet(file, 0);
//		System.out.println("list中的数据打印出来");
//		for (int i = 0; i < excelList.size(); i++) {
//			System.out.println(excelList.get(i));
//		}
		


//		try {
//			// 创建一个URL实例
//			URL url = new URL("http://file.cardezu.com/upload/manage/2020/04/01/14351115.xls");
//			InputStream is = url.openStream();
//			InputStreamReader isr = new InputStreamReader(is, "utf-8");
//			BufferedReader br = new BufferedReader(isr);
//			StringBuilder sb = new StringBuilder();
//			String data = br.readLine();// 读取数据
//			while (data != null) {// 循环读取数据
//				sb.append(data);
//				data = br.readLine();
//			}
//			br.close();
//			isr.close();
//			is.close();
//			System.out.println(sb.toString());
//		} catch (IOException e) {
//			System.out.println("文件读取错误");
//		}
		
		new ReadExcelUtil().readExcelByUrl("http://file.cardezu.com/upload/manage/2020/04/01/14351115.xls");
	}

	// 去读Excel的方法readExcel，该方法的入口参数为一个File对象
	@SuppressWarnings("unused")
	public List<String> readExcel(File file) {
		try {
			// 创建输入流，读取Excel
			InputStream is = new FileInputStream(file.getAbsolutePath());
			// jxl提供的Workbook类
			Workbook wb = Workbook.getWorkbook(is);
			// Excel的页签数量
			int sheet_size = wb.getNumberOfSheets();
			for (int index = 0; index < sheet_size; index++) {
				List<String> outerList = new ArrayList<String>();
				// 每个页签创建一个Sheet对象
				Sheet sheet = wb.getSheet(index);
				// sheet.getRows()返回该页的总行数
				for (int i = 0; i < sheet.getRows(); i++) {
					// sheet.getColumns()返回该页的总列数
					String rowInfo = "";
					for (int j = 0; j < sheet.getColumns(); j++) {
						String cellinfo = sheet.getCell(j, i).getContents();
						if (cellinfo.isEmpty()) {
							continue;
						}
						rowInfo += cellinfo;
					}
					outerList.add(rowInfo);
					System.out.println();
				}
				return outerList;
			}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (BiffException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return null;
	}

	// 去读Excel的方法readExcel，该方法的入口参数为一个File对象
	@SuppressWarnings("unused")
	public List<String> readExcelByUrl(String fileUrl) {
		try {
			// 创建输入流，读取Excel
			URL url = new URL(fileUrl);
			InputStream is = url.openStream();
			// jxl提供的Workbook类
			Workbook wb = Workbook.getWorkbook(is);
			// Excel的页签数量
			int sheet_size = wb.getNumberOfSheets();
			for (int index = 0; index < sheet_size; index++) {
				List<String> outerList = new ArrayList<String>();
				// 每个页签创建一个Sheet对象
				Sheet sheet = wb.getSheet(index);
				System.out.println("sheetName----" + sheet.getName());
				// sheet.getRows()返回该页的总行数
				for (int i = 0; i < sheet.getRows(); i++) {
					Cell[] cells = sheet.getRow(i);
					System.out.println("row----" + i + "---" + JSON.toJSONString(cells));
					// sheet.getColumns()返回该页的总列数
					String rowInfo = "";
					for (int j = 0; j < sheet.getColumns(); j++) {
						Cell cell = cells[j];
						String cellContents = cell.getContents();
						if (cellContents.isEmpty()) {
							continue;
						}
						rowInfo += cellContents;
					}
					outerList.add(rowInfo);
					System.out.println();
				}
				return outerList;
			}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (BiffException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * 获取表格中的表单
	 * @param fileUrl 完整的URL地址，如：http://file.cardezu.com/upload/manage/2020/04/01/14351115.xls
	 * @param sheetIndex 表单序号，从0开始
	 * @return
	 */
	public static Sheet getSheetByUrl(String fileUrl, int sheetIndex) {
		try {
			// 创建输入流，读取Excel
			URL url = new URL(fileUrl);
			InputStream is = url.openStream();
			// jxl提供的Workbook类
			Workbook wb = Workbook.getWorkbook(is);
			// Excel的页签数量
			int sheet_size = wb.getNumberOfSheets();
			if (sheet_size == 0) {
				return null;
			} else {
				return wb.getSheet(sheetIndex);
			}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (BiffException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return null;
	}

	// 去读Excel的方法readExcel，该方法的入口参数为一个File对象
	public static List<Map<Integer, String>> readSheet(File file, int sheetIndex) {
		try {
			// 创建输入流，读取Excel
			InputStream is = new FileInputStream(file.getAbsolutePath());
			// jxl提供的Workbook类
			Workbook wb = Workbook.getWorkbook(is);

			List<Map<Integer, String>> outerList = new ArrayList<Map<Integer, String>>();
			
			// 每个页签创建一个Sheet对象
			Sheet sheet = wb.getSheet(sheetIndex);
			// sheet.getRows()返回该页的总行数
			for (int i = 0; i < sheet.getRows(); i++) {
				Map<Integer, String> rowMap = new HashMap<Integer, String>();
				// sheet.getColumns()返回该页的总列数
				for (int j = 0; j < sheet.getColumns(); j++) {
					String cellinfo = sheet.getCell(j, i).getContents();
					if (cellinfo.isEmpty()) {
						continue;
					}
					
					rowMap.put(j, cellinfo);
				}
				outerList.add(rowMap);
			}
			return outerList;
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (BiffException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * 
	 * @param fieldNameArr	字段名数组
	 * @param sheet 表格的表单
	 * @param startRow 起始行，1代表第二行
	 * @return
	 */
	public static List<Map<String, Object>> sheetToJAFromSecondLine(String[] fieldNameArr, Sheet sheet, Integer startRow) {
		
		List<Map<String, Object>> listMap = new ArrayList<Map<String, Object>>();
		
		// 总行数
		int rows = sheet.getRows();
		if (rows < 1) {
			return null;
		}
		
		// 遍历sheet
		for (int i = startRow; i < rows; i++) {
			// 获取Row并遍历
			Cell[] cells = sheet.getRow(i);
			
			Map<String, Object> map = new HashMap<String, Object>();
			for (int j = 0; j < cells.length; j++) {
				Cell cell = cells[j];
				String contents = cell.getContents();
				String fieldName = fieldNameArr[j];
				if (contents != null && contents.length() > 0) {
					map.put(fieldName, contents);
				}
				
			}
			listMap.add(map);
		}
		
		return listMap;
	}
}